﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using ANZ_AIM_DAL;
using ANZ_AIM_BLL.Model;
using ANZ_AIM_BLL.Utils;
using ANZ_AIM_FRAMEWORK;

namespace ANZ_AIM_BLL
{
    public class SalePaymentBLL
    {
        private string ConnectionString;
        private const string TABLE_NAME = "SalePayment";
        private Dictionary<string, string> ObjectPropDict;

        public SalePaymentBLL()
        {
            ConnectionString = DBHelper.GetMainMdbConnectionString();
            this.ObjectPropDict = BuildObjectPropertyMapping();
        }

        public void SavePayment(List<SalePayment> salePaymentList)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters;
                string command = string.Empty;
                foreach (SalePayment salePayment in salePaymentList)
                {
                    command = CommandHelper.BuildInsertCommand(salePayment, dict, out parameters);
                    //command = CommandHelper.BuildInsertCommand(salePayment, dict, TABLE_NAME, out parameters);
                    OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);                  
                }
            }
        catch (Exception ex) { throw; }
        }

        public DataTable GetByBatchFileSaleInfoID(string batchFileSaleInfoID)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                SalePayment salePayment = new SalePayment() { BatchFileSaleInfoID = batchFileSaleInfoID };
                OleDbParameter[] parameters;
                var conditionDict = new Dictionary<string, string>
                    {
                        {"BatchFileSaleInfoID", "BatchFileSaleInfoID"}
                    };
                string command = CommandHelper.BuildSelectCommand(salePayment, dict, conditionDict, out parameters);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);

                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }

        public string GetSaleCodeMapping(string saleCode)
        {
            try
            {
                string command = string.Format("Select top 1 [ProfileID] FROM {0} WHERE SaleCode = '{1}'", TABLE_NAME, saleCode);
                DataTable dt = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command).Tables[0];
                return dt.Rows.Count > 0 ? dt.Rows[0][0].ToString() : "";
            }
            catch (Exception ex) { throw; }
        }

        public DataTable Search(string personalCode, string fromDate, string toDate)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                string command = string.Empty;
                command = CommandHelper.BuildSelectAllCommand(TABLE_NAME, dict);
                command += " WHERE (@personalCode IS NULL OR ProfileID = @PersonalCode)";
                command += " AND Month BETWEEN @FromDate AND @ToDate";

                OleDbParameter[] parameters = new OleDbParameter[3];

                if (string.IsNullOrEmpty(personalCode))
                    parameters[0] = new OleDbParameter("@personalCode", DBNull.Value);
                else
                    parameters[0] = new OleDbParameter("@personalCode", personalCode);
                parameters[1] = new OleDbParameter("@FromDate", fromDate);
                parameters[2] = new OleDbParameter("@ToDate", toDate);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);

                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }

        public Dictionary<string, string> BuildObjectPropertyMapping()
        {
            Dictionary<string, string> dictionary = new Dictionary<string, string>
            {
                {"Month", "Month"},
                {"PayMonth", "PayMonth"},
                {"IncMonth", "IncMonth"},
                {"SaleCode", "SaleCode"},
                {"PersonalCode", "PersonalCode"},
                {"EmployeeFullNameE", "EmployeeFullNameE"},
                {"ContractSalary", "ContractSalary"},
                {"BUIPNetScore", "BUIPNetScore"},
                {"PerformanceGrade", "PerformanceGrade"},
                {"PerfIncentive", "PerfIncentive"},
                {"DeferralPay", "DeferralPay"},
                {"SQMDeduction", "SQMDeduction"},
                {"SQMBonus", "SQMBonus"},
                {"InactiveCardClawback", "InactiveCardClawback"},
                {"Adjustment", "Adjustment"},
                {"IncentiveAct", "IncentiveAct"},
                {"IncentivePaid", "IncentivePaid"},
                {"ActualWDs", "ActualWDs"},
                {"PaidWDs", "PaidWDs"},
                {"Base", "Base"},
                {"Allowances", "Allowances"},
                {"_13thSalaryQuitSettlement", "_13thSalaryQuitSettlement"},
                {"NonIncentives", "NonIncentives"},
                {"SHUIUnionFee", "SHUIUnionFee"},
                {"Insurance", "Insurance"},
                {"TotalInsurances", "TotalInsurances"},
                {"HRServiceFee", "HRServiceFee"},
                {"GrossEmployeeIncome", "GrossEmployeeIncome"},
                {"PayrollCost", "PayrollCost"},
                {"Remarks", "Remarks"},
                {"BatchFileSaleInfoID", "BatchFileSaleInfoID"},   
                {"ProfileID", "ProfileID"} 
            };

            return dictionary;
        }
    }
}
